﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Web;

public class CreateSchema
{
  #region SQL to make the tables
  const string sql = @"
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'people')
BEGIN
 
CREATE TABLE [dbo].[people](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[sid] [varchar](250)  NULL,
	[created] [varchar](250)  NULL,
	[ip_address] [varchar](250)  NULL,
	[uid] [varchar](250)  NULL,
	[username] [varchar](250)  NULL,
	[first_name] [varchar](250)  NULL,
	[last_name] [varchar](250)  NULL,
	[email] [varchar](250)  NULL,
	[cell_mobile] [varchar](250)  NULL,
	[street] [varchar](250)  NULL,
	[city] [varchar](250)  NULL,
	[state] [varchar](250)  NULL,
	[postcode] [varchar](250)  NULL,
	[country] [varchar](250)  NULL,
	[home_phone] [varchar](250)  NULL,
	[age] [varchar](250)  NULL,
	[gender] [varchar](250)  NULL,
 CONSTRAINT [PK_people] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF)
)

end



IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users')
BEGIN

CREATE TABLE [users](
	[email] [varchar](250)  NOT NULL,
	[password] [varchar](250)  NULL,
  CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
  (
	  [email] ASC
  )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF)
);

insert into users(email,password)
values ('chris.hulbert@gmail.com','e7f0df4d064a7d2cdc653447e752cf4d736e114b');
insert into users(email,password)
values ('ben.tam@hillsong.com','e7f0df4d064a7d2cdc653447e752cf4d736e114b');

end

-- 7 June 2010, add notes field to people
if not exists (select * from INFORMATION_SCHEMA.columns where table_name = 'people' and column_name = 'notes') begin
  ALTER TABLE people ADD notes text NULL
end


-- 11 june 2010, add tables for age groups and involvements

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'age_groups')
begin
  CREATE TABLE dbo.age_groups
	  (
	  age_group varchar(50) NOT NULL,
	  sort_order int NOT NULL
	  );
  ALTER TABLE dbo.age_groups ADD CONSTRAINT
	  DF_age_groups_sort_order DEFAULT 0 FOR sort_order;
  ALTER TABLE dbo.age_groups ADD CONSTRAINT
	  PK_age_groups PRIMARY KEY CLUSTERED 
	  (
	  age_group
	  );
  insert into age_groups(age_group,sort_order) values('Babies',10);
  insert into age_groups(age_group,sort_order) values('Cubby House',20);
  insert into age_groups(age_group,sort_order) values('The Ark',30);
  insert into age_groups(age_group,sort_order) values('Fun House',40);
  insert into age_groups(age_group,sort_order) values('All Stars',50);
  insert into age_groups(age_group,sort_order) values('Voltage',60);
  insert into age_groups(age_group,sort_order) values('Fuel',70);
  insert into age_groups(age_group,sort_order) values('Wildlife',80);
  insert into age_groups(age_group,sort_order) values('Powerhouse',90);
  insert into age_groups(age_group,sort_order) values('Frontline',100);
  insert into age_groups(age_group,sort_order) values('Adults',110);
end

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'involvements')
begin
  CREATE TABLE dbo.involvements
	  (
	  involvement varchar(50) NOT NULL,
	  sort_order int NOT NULL
	  );
  ALTER TABLE dbo.involvements ADD CONSTRAINT
	  DF_involvements_sort_order DEFAULT 0 FOR sort_order;
  ALTER TABLE dbo.involvements ADD CONSTRAINT
	  PK_involvements PRIMARY KEY CLUSTERED 
	  (
	  involvement
	  );
  insert into involvements(involvement,sort_order) values('Key Team',10);
  insert into involvements(involvement,sort_order) values('Leadership',20);
  insert into involvements(involvement,sort_order) values('Ministry Active',30);
  insert into involvements(involvement,sort_order) values('Connect Group Active',40);
  insert into involvements(involvement,sort_order) values('Service Active',50);
  insert into involvements(involvement,sort_order) values('Service Inactive',60);
  insert into involvements(involvement,sort_order) values('NPN: New People',70);
  insert into involvements(involvement,sort_order) values('NPN: New Christian',80);
  insert into involvements(involvement,sort_order) values('Guest',90);
  insert into involvements(involvement,sort_order) values('Community',100);
  insert into involvements(involvement,sort_order) values('Mailing List',110);
  insert into involvements(involvement,sort_order) values('Uncontactable',120);
end

if not exists (select * from INFORMATION_SCHEMA.columns where table_name = 'people' and column_name = 'involvement') begin
  ALTER TABLE people ADD involvement varchar(250) NULL;
end

if not exists (select * from INFORMATION_SCHEMA.columns where table_name = 'people' and column_name = 'age_group') begin
  ALTER TABLE people ADD age_group varchar(250) NULL;
end


-- 13 June 2010, add table and sample data for filters
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'filters')
begin
  CREATE TABLE [dbo].[filters](
	  [id] [int] IDENTITY(1,1) NOT NULL,
	  [filter] [varchar](250)  NOT NULL,
	  [where_clause] [text]  NOT NULL,
    CONSTRAINT [PK_filters] PRIMARY KEY CLUSTERED 
    (
	    [id] ASC
    )
  );
  insert into filters(filter,where_clause) values('Men','gender like ''m%''');
  insert into filters(filter,where_clause) values('Women','gender like ''f%''');
  insert into filters(filter,where_clause) values('Youth','age_group in (''Fuel'',''Wildlife'',''Powerhouse'')');
end


-- 14 July 2010, add permissions for users 
if not exists (select * from INFORMATION_SCHEMA.columns where table_name = 'users' and column_name = 'admin') begin
  ALTER TABLE dbo.users ADD	admin bit NULL
  exec('update users set admin=1 where email in (''ben.tam@hillsong.com'',''chris.hulbert@gmail.com'')')
end


";
  #endregion

  /// <summary>
  /// Create the tables if they're missing
  /// </summary>
  public static void Go()
	{
    using (SqlConnection conn = Conn.GetOpened())
    {
      new SqlCommand(sql, conn).ExecuteNonQuery();
    }
	}
}
